﻿CREATE SEQUENCE pca_courier.seq_manifiesto_id;
CREATE TABLE pca_courier.manifiestos
(
	manifiesto_id bigint NOT NULL DEFAULT pca_global.fn_pseudo_encrypt_bigint((nextval('pca_courier.seq_manifiesto_id'))::integer),
	sesion_id bigint NOT NULL,
	serie integer NOT NULL,
	numero integer NOT NULL,
	estacion_id integer NOT NULL,
	agente_id bigint,--No se permite despachar Manifiesto si no se espeficica el agente
	fecha_hora_despacho timestamp with time zone,--La Fecha y hora en que sale a ruta
	fecha_hora_regreso timestamp with time zone,--La Fecha hora que regresa
	fecha_hora_sistema timestamp with time zone NOT NULL DEFAULT current_timestamp,
	estado_manifiesto_id varchar(2) NOT NULL,
	observaciones text,
	registros integer NOT NULL DEFAULT 0,--representa el total de registros enviados
	retornos integer NOT NULL DEFAULT 0,--representa el total de registros que regresaron
	CONSTRAINT manifiestos_pk PRIMARY KEY(manifiesto_id),
	CONSTRAINT manifiestos_key UNIQUE(serie,numero),
	CONSTRAINT sesiones_fkey FOREIGN KEY(sesion_id)
		REFERENCES pca_log.sesiones(sesion_id) MATCH SIMPLE
		ON UPDATE RESTRICT ON DELETE RESTRICT,
	CONSTRAINT estaciones_fkey FOREIGN KEY(estacion_id)
		REFERENCES pca_courier.estaciones(estacion_id)
		ON UPDATE RESTRICT ON DELETE RESTRICT,
	CONSTRAINT agentes_fkey FOREIGN KEY(agente_id)
		REFERENCES pca_courier.agentes(agente_id) MATCH SIMPLE
		ON UPDATE RESTRICT ON DELETE RESTRICT,
	CONSTRAINT estado_manifiesto_fkey FOREIGN KEY(estado_manifiesto_id)
		REFERENCES pca_courier.estado_manifiesto(estado_manifiesto_id) MATCH SIMPLE
		ON UPDATE RESTRICT ON DELETE RESTRICT
);

-----------------------------------------------------------------------------------------------
-----------------------------------------Triggers----------------------------------------------
-----------------------------------------------------------------------------------------------

--permite llevar un conteo de cuantos manifiestos estan asociados a una estacion
CREATE OR REPLACE FUNCTION pca_courier.fn_count_manifiesto_estaciones() RETURNS trigger AS
$$
BEGIN
	IF(TG_OP='INSERT')THEN
		UPDATE pca_courier.estaciones SET
			contador=contador+1
		WHERE
			estacion_id=NEW.estacion_id;
	ELSIF(TG_OP='DELETE')THEN
		UPDATE pca_courier.estaciones SET
			contador=contador-1
		WHERE
			estacion_id=OLD.estacion_id;
	ELSIF(TG_OP='UPDATE')THEN
		IF(NEW.estacion_id<>OLD.estacion_id) THEN
			UPDATE pca_courier.estaciones SET
				contador=contador-1
			WHERE
				estacion_id=OLD.estacion_id;
	
			UPDATE pca_courier.estaciones SET
				contador=contador+1
			WHERE
				estacion_id=NEW.estacion_id;
		END IF;
	END IF;
	RETURN NULL;
END;
$$
LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS tri_count_manifiesto_estaciones ON pca_courier.manifiestos;
CREATE TRIGGER tri_count_manifiesto_estaciones AFTER INSERT OR DELETE OR UPDATE ON pca_courier.manifiestos
FOR EACH ROW
EXECUTE PROCEDURE pca_courier.fn_count_manifiesto_estaciones();

--Contador para la tabla pca_courier.manifiestos
DROP TRIGGER IF EXISTS tri_count_rows_manifiestos ON pca_courier.manifiestos;
CREATE TRIGGER tri_count_rows_manifiestos AFTER INSERT OR DELETE ON pca_courier.manifiestos
FOR EACH ROW
EXECUTE PROCEDURE pca_tablas.fn_count_rows('pca_courier.manifiestos');

--INSERT INTO pca_tablas.count_rows_tables VALUES('pca_courier.manifiestos',0);

-----------------------------------------------------------------------------------------------
------------------------------------------Vistas-----------------------------------------------
-----------------------------------------------------------------------------------------------

CREATE OR REPLACE VIEW pca_courier.vw_manifiestos AS
SELECT
	m.manifiesto_id,
	m.sesion_id,
	m.serie,
	m.numero,
	pca_global.fn_get_strperiodonumero(m.serie,m.numero) AS strserienumero,
	m.estacion_id,
	e.nombre as estacion,
	m.agente_id,
	a.nombre as agente,
	m.fecha_hora_despacho,
	m.fecha_hora_regreso,
	m.fecha_hora_sistema,
	m.estado_manifiesto_id,
	m.observaciones,
	m.registros,
	m.retornos,
	(m.registros-m.retornos) as faltantes
FROM
	pca_courier.manifiestos m
LEFT JOIN
	pca_courier.estaciones e
ON
	(m.estacion_id=e.estacion_id)
LEFT JOIN
	pca_courier.agentes a
ON
	(m.agente_id=a.agente_id);

-----------------------------------------------------------------------------------------------
----------------------------------------Funciones----------------------------------------------
-----------------------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION pca_courier.fn_existe_manifiesto(integer,--serie $1
							    integer --numero $2
							    )RETURNS boolean AS
$$
DECLARE
	_manifiesto_id bigint;
BEGIN
	SELECT INTO 
		_manifiesto_id manifiesto_id
	FROM 
		pca_courier.manifiestos
	WHERE 
		serie=$1 AND
		numero=$2;

	IF(FOUND)THEN
		RETURN true;
	ELSE
		RETURN false;
	END IF;
END;
$$
LANGUAGE plpgsql;

--Registra un manifiesto y por defecto coloca 00 como estado
CREATE OR REPLACE FUNCTION pca_courier.fn_add_manifiesto(bigint,--sesion_id $1
							 integer,--serie $2
							 integer,--numero $3
							 integer,--estacion_id $4
							 varchar --observaciones $5
							 )RETURNS integer AS
$$
DECLARE
	_row RECORD;
BEGIN
	SELECT INTO
		_row
		manifiesto_id
	FROM
		pca_courier.manifiestos
	WHERE
		serie=$2 AND
		numero=$3;

	IF(NOT FOUND)THEN
		INSERT INTO pca_courier.manifiestos
			(sesion_id,
			serie,
			numero,
			estacion_id,
			estado_manifiesto_id,
			observaciones)
		VALUES
			($1,$2,$3,$4,'00',$5);

		RETURN currval('pca_courier.seq_manifiesto_id');
	ELSE
		RAISE EXCEPTION 'Ya existe el manifiesto N° %, revice su correlativo',pca_global.fn_get_strperiodonumero($2,$3);
	END IF;
END;
$$
LANGUAGE plpgsql;

--Actualiza la informacion del manifiesto
CREATE OR REPLACE FUNCTION pca_courier.fn_update_manifiesto(bigint,--manifiesto_id $1
							    bigint,--sesion_id $2
							    integer,--serie $3
							    integer,--numero $4
							    integer,--estacion_id $5
							    varchar --observaciones $6
							    )RETURNS void AS
$$
DECLARE
	_estado_manifiesto_id pca_courier.manifiestos.estado_manifiesto_id%TYPE;
BEGIN
	SELECT INTO 
		_estado_manifiesto_id estado_manifiesto_id 
	FROM 
		pca_courier.manifiestos 
	WHERE 
		manifiesto_id=$1;

	IF(FOUND)THEN
		IF(_estado_manifiesto_id='00')THEN
			
			UPDATE pca_courier.manifiestos SET
				sesion_id=$2,
				serie=$3,
				numero=$4,
				estacion_id=$5,
				fecha_hora_sistema=current_timestamp,
				observaciones=$6
			WHERE
				manifiesto_id=$1;
		ELSE
			RAISE EXCEPTION 'No se puede modificar manifiesto con estado diferente de REGISTRADO';
		END IF;
	ELSE
		RAISE EXCEPTION 'No existe manifiesto con ID= %',$1;
	END IF;
EXCEPTION
	WHEN unique_violation THEN
		RAISE EXCEPTION 'Ya existe el manifiesto N° %, revice su correlativo',pca_global.fn_get_strperiodonumero($2,$3);
END;
$$
LANGUAGE plpgsql;

--Actualiza el estado del manifiesto
CREATE OR REPLACE FUNCTION pca_courier.fn_update_estado_manifiesto(bigint,--manifiesto_id $1
							           varchar --estado_manifiesto_id $2
								   )RETURNS void AS
$$
DECLARE
	_row pca_courier.manifiestos%ROWTYPE;
	_retornos integer;
BEGIN
	IF($1=0)THEN
		RAISE EXCEPTION 'El manfiesto 00-000000 se encuentra bloqueado y no se puede modificar';
	ELSE
		SELECT INTO
			_row
			*
		FROM
			pca_courier.manifiestos
		WHERE
			manifiesto_id=$1;
		
		IF(_row.estado_manifiesto_id='00')THEN
			RAISE EXCEPTION 'El manifiesto N° % tiene estado=REGISTRADO, no se puede cambiar estado manualmente',pca_courier.fn_get_strperiodonumero_manifiesto($1);
		ELSIF(_row.estado_manifiesto_id='01' OR _row.estado_manifiesto_id='02')THEN
			IF($2='00')THEN --regresar a estado registrado
				SELECT INTO
					_retornos
					retornos
				FROM
					pca_courier.vw_manifiestos
				WHERE
					manifiesto_id=$1;

				IF(FOUND)THEN
					IF(_retornos=0)THEN
						--actualizamos el estado del manifiesto
						UPDATE pca_courier.manifiestos SET
							estado_manifiesto_id=$2
						WHERE
							manifiesto_id=$1;

						--regresar a clasificacion todos los consignados
						UPDATE 
							pca_courier.consignados
						SET
							estado_reparto_id='02'
						WHERE
							manifiesto_id=$1;

						--borras todos los motivos del manifiesto
						/*UPDATE	 
							pca_courier.manifiesto_consignados
						SET
							fecha_hora_regreso=null,
							tipo_descargo_id=null
						WHERE
							manifiesto_id=$1;*/
					ELSE
						RAISE EXCEPTION 'Existen % DESCARGOS no se puede cambiar estado al manifiesto',_retornos;
					END IF;
				ELSE
					RAISE EXCEPTION 'No existe manifiesto con ID=%',$1;
				END IF;
			ELSIF(_row.estado_manifiesto_id='02' AND $2='01')THEN --solo cambiamos el estado a transito
				UPDATE pca_courier.manifiestos SET
					estado_manifiesto_id=$2
				WHERE
					manifiesto_id=$1;
			ELSIF(_row.estado_manifiesto_id='01' AND $2='02')THEN 
				RAISE EXCEPTION 'El manifiesto N° % tiene estado=TRANSITO, solo se puede cambiar a estado=REGISTRADO',pca_courier.fn_get_strperiodonumero_manifiesto($1);
			ELSE
				RAISE EXCEPTION 'El manifiesto N° % , no se puede actualizar a estado=%, consulte al administrador',pca_courier.fn_get_strperiodonumero_manifiesto($1),
														            pca_courier.fn_get_nombre_estado_manifiesto($2);
			END IF;
		ELSIF(_row.estado_manifiesto_id='99')THEN
			RAISE EXCEPTION 'El manifiesto N° % esta ANULADO no se permite cambiar estado',pca_courier.fn_get_strperiodonumero_manifiesto($1);
		END IF;
	END IF;
END;
$$
LANGUAGE plpgsql;

--Devuelve el id del manifiesto a partir de la serie y numero
CREATE OR REPLACE FUNCTION pca_courier.fn_get_manifiesto_id(integer,--serie manifiesto $1
							    integer --numero manifiesto $2
							    )RETURNS integer AS
$$
DECLARE
	_manifiesto_id pca_courier.manifiestos.manifiesto_id%TYPE;
BEGIN
	SELECT INTO 
		_manifiesto_id manifiesto_id 
	FROM 
		pca_courier.manifiestos 
	WHERE 
		serie=$1 AND numero=$2;

	IF(FOUND) THEN
		RETURN _manifiesto_id;
	ELSE
		RAISE EXCEPTION 'No existe manifiesto numero: %',pca_global.fn_get_strperiodonumero($1,$2);
	END IF;
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION pca_courier.fn_get_manifiesto(bigint --manifiesto_id $1
							 )RETURNS SETOF pca_courier.vw_manifiestos AS
$$
DECLARE
	row_manifiesto pca_courier.vw_manifiestos%ROWTYPE;
BEGIN
	FOR row_manifiesto IN
		SELECT
			manifiesto_id,
			sesion_id,
			serie,
			numero,
			strserienumero,
			estacion_id,
			estacion,
			agente_id,
			agente,
			fecha_hora_despacho,
			fecha_hora_regreso,
			fecha_hora_sistema,
			estado_manifiesto_id,
			observaciones,
			registros,
			retornos,
			faltantes
		FROM
			pca_courier.vw_manifiestos
		WHERE
			manifiesto_id=$1
	LOOP
		RETURN NEXT row_manifiesto;
	END LOOP;
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION pca_courier.fn_get_manifiesto(integer,--serie manifiesto $1
							 integer --numero manifiesto $2
							 )RETURNS SETOF pca_courier.vw_manifiestos AS
$$
DECLARE
	row_manifiesto pca_courier.vw_manifiestos%ROWTYPE;
BEGIN
	FOR row_manifiesto IN
		SELECT
			manifiesto_id,
			sesion_id,
			serie,
			numero,
			strserienumero,
			estacion_id,
			estacion,
			agente_id,
			agente,
			fecha_hora_despacho,
			fecha_hora_regreso,
			fecha_hora_sistema,
			estado_manifiesto_id,
			observaciones,
			registros,
			retornos,
			faltantes
		FROM
			pca_courier.vw_manifiestos
		WHERE
			manifiesto_id=pca_courier.fn_get_manifiesto_id($1,$2)
	LOOP
		RETURN NEXT row_manifiesto;
	END LOOP;
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION pca_courier.fn_get_strperiodonumero_manifiesto(bigint--manifiesto_id $1
									  )RETURNS varchar AS
$$
DECLARE
	_row pca_courier.manifiestos%ROWTYPE;
BEGIN
	SELECT INTO
		_row
		*
	FROM
		pca_courier.manifiestos
	WHERE
		manifiesto_id=$1;

	IF(FOUND)THEN
		RETURN pca_global.fn_get_strperiodonumero(_row.serie,_row.numero);
	ELSE
		RAISE EXCEPTION 'No existe manifiesto con ID: %',$1;
	END IF;
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION pca_courier.fn_get_manifiesto_estado(bigint--manifiesto_id $1
								)RETURNS varchar AS
$$
DECLARE
	_estado_manifiesto_id pca_courier.manifiestos.estado_manifiesto_id%TYPE;
BEGIN
	SELECT INTO
		_estado_manifiesto_id
		estado_manifiesto_id
	FROM
		pca_courier.manifiestos
	WHERE
		manifiesto_id=$1;

	IF(FOUND)THEN
		RETURN _estado_manifiesto_id;
	ELSE
		RAISE EXCEPTION 'No existe manifiesto con ID = %',$1;
	END IF;
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION pca_courier.fn_get_resumen_estado_manifiesto()RETURNS SETOF RECORD AS
$$
DECLARE
	_resumen RECORD;
	_resul RECORD;
	count_registrado bigint;
	count_transito bigint;
	count_finalizado bigint;
	count_anulado bigint;
BEGIN
	count_registrado := 0;
	count_transito := 0;
	count_finalizado := 0;
	count_anulado := 0;

	FOR _resumen IN
		SELECT 
			estado_manifiesto_id,count(estado_manifiesto_id) as total 
		FROM 
			pca_courier.manifiestos 
		GROUP BY 
			estado_manifiesto_id
	LOOP
		IF(_resumen.estado_manifiesto_id='00')THEN
			count_registrado := _resumen.total;
		ELSIF(_resumen.estado_manifiesto_id='01')THEN
			count_transito := _resumen.total;
		ELSIF(_resumen.estado_manifiesto_id='02')THEN
			count_finalizado := _resumen.total;
		ELSIF(_resumen.estado_manifiesto_id='99')THEN
			count_anulado := _resumen.total;
		END IF;
	END LOOP;

	FOR _resul IN
		SELECT
			count_registrado,
			count_transito,
			count_finalizado,
			count_anulado
	LOOP
		RETURN NEXT _resul;
	END LOOP;
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION pca_courier.fn_update_manifiesto_agente(bigint, --manifiesto_id $1
								   bigint --agente_id $2
								   ) RETURNS void AS
$$
DECLARE
	_row pca_courier.manifiestos%ROWTYPE;
BEGIN
	SELECT INTO
		_row
		*
	FROM
		pca_courier.manifiestos
	WHERE
		manifiesto_id=$1;

	IF(FOUND)THEN
		IF(_row.estado_manifiesto_id='01')THEN
			IF(_row.retornos=0)THEN
				UPDATE 
					pca_courier.manifiestos
				SET
					estado_manifiesto_id='01',
					agente_id=$2,
					fecha_hora_despacho=current_timestamp
				WHERE
					manifiesto_id=$1;
			ELSE
				RAISE EXCEPTION 'No se cambiar el agente al manifiesto N° %, existen % registros descargados, consulte al administrador del sistema',pca_courier.fn_get_strperiodonumero_manifiesto($1),_row.retornos;
			END IF;
		ELSE
			RAISE EXCEPTION 'Solo se puede cambiar agente a manifiestos con con estado = TRANSITO, estado_actual = %',pca_courier.fn_get_nombre_estado_manifiesto(_estado_manifiesto_id);
		END IF;
	ELSE
		RAISE EXCEPTION 'No existe manifiesto con ID: %',$1;
	END IF;
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION pca_courier.fn_get_list_manifiestos(integer,--start $1
								integer --limit $2
								)RETURNS SETOF refcursor AS
$$
DECLARE
	cursor_result refcursor;
	cursor_totalrows refcursor;
BEGIN
	OPEN cursor_result FOR
		SELECT
			manifiesto_id,
			sesion_id,
			serie,
			numero,
			strserienumero,
			estacion_id,
			estacion,
			agente_id,
			agente,
			fecha_hora_despacho,
			fecha_hora_regreso,
			fecha_hora_sistema,
			estado_manifiesto_id,
			observaciones,
			registros,
			retornos,
			faltantes
		FROM
			pca_courier.vw_manifiestos
		ORDER BY
			serie DESC,numero DESC LIMIT $2 OFFSET $1;
	RETURN NEXT cursor_result;

	OPEN cursor_totalrows FOR
		SELECT pca_tablas.fn_get_count_rows_table('pca_courier.manifiestos');
	RETURN NEXT cursor_totalrows;	
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION pca_courier.fn_get_list_manifiestos(integer,--start $1
							       integer, --limit $2
							       varchar[] --filtros $3
							       ) RETURNS SETOF refcursor AS
$$
DECLARE
	cursor_result refcursor;
	cursor_totalrows refcursor;
	sqlfiltro varchar;
	sqlcount varchar;
BEGIN
	sqlfiltro := 
		'SELECT
			manifiesto_id,
			sesion_id,
			serie,
			numero,
			strserienumero,
			estacion_id,
			estacion,
			agente_id,
			agente,
			fecha_hora_despacho,
			fecha_hora_regreso,
			fecha_hora_sistema,
			estado_manifiesto_id,
			observaciones,
			registros,
			retornos,
			faltantes
		FROM
			pca_courier.vw_manifiestos
		WHERE '|| 
			pca_global.fn_get_strfiltro($3)|| 
		' ORDER BY
			serie DESC,numero DESC LIMIT '|| $2 || ' OFFSET '|| $1 ;

	sqlcount := 
		'SELECT
			count(manifiesto_id)
		FROM
			pca_courier.vw_manifiestos
		WHERE '|| 
			pca_global.fn_get_strfiltro($3);
	
	OPEN cursor_result FOR
		EXECUTE(sqlfiltro);
	RETURN NEXT cursor_result;

	OPEN cursor_totalrows FOR
		EXECUTE(sqlcount);
	RETURN NEXT cursor_totalrows;	
END;
$$
LANGUAGE plpgsql;